<?php
require_once 'models/model_manager.php';
require_once 'models/entite_historique.php';
require_once 'models/date_historique.php';
require_once 'models/date_historique_manager.php';
require_once 'models/zone_geographique_manager.php';

class entite_historique_manager extends Manager
{
	protected $_date;
	protected $_zone;
	public function __construct($db) {
		parent::__construct($db);
		$this->_date = new date_historique_manager($this->_db);
		$this->_zone = new zone_geographique_manager($this->_db);
	}
	
	public function get_by_id($i) {
		$query = "SELECT * FROM entite_historique WHERE idEntite = " . $i;
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		$data = oci_fetch_assoc($result);
		$entite = new entite_historique($data);
		$this->set_date_creation($entite);
		$this->set_date_dissolution($entite);
		return $entite;		
	}
	
	public function get_all() {
		$entites = array();
		$query = "SELECT * FROM entite_historique";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		while ($data = oci_fetch_assoc($result)) {
			$entites[] = new entite_historique($data);
		}
		foreach ($entites as $e) {
			$this->set_date_creation($e);
			$this->set_date_dissolution($e);
		}
		return $entites;
	}
	
	public function set_date_creation($e) {
		$e->set_creation($this->get_date_creation($e->get_identite()));
	}
	
	public function set_date_dissolution($e) {
		$e->set_dissolution($this->get_date_dissolution($e->get_identite()));
	}
	
	public function get_date_creation($id) {
		$query =
			"SELECT idDate, reference, to_char(\"DATE\", 'DD/MM/SYYYY') AS \"DATE\" FROM date_historique
			WHERE reference = 1
				AND idDate IN (SELECT idDate FROM creation
								WHERE idEntite = " . $id . ")";
		$result = oci_parse($this->_db, $query);
		
		oci_execute($result);
		$d = oci_fetch_assoc($result);
		return new date_historique($d);
	}
	
	public function get_all_date_creation($id) {
		$creations = array();
		$query = 
			"SELECT idDate, reference, to_char(\"DATE\", 'DD/MM/SYYYY') AS \"DATE\" FROM date_historique
			WHERE idDate IN (SELECT idDate FROM creation
								WHERE idEntite = " . $id . ')';
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		while ($data = oci_fetch_assoc($result)) {
			$creations[] = new date_historique($data);
		}
		return $creations;
	}
	
	public function get_date_dissolution($id) {
		$query =
			"SELECT idDate, reference, to_char(\"DATE\", 'DD/MM/SYYYY') AS \"DATE\" FROM date_historique
			WHERE reference = 1
				AND idDate IN (SELECT idDate FROM dissolution
								WHERE idEntite = " . $id . ")";
		$result = oci_parse($this->_db, $query);
		
		oci_execute($result);
		$d = oci_fetch_assoc($result);
		return new date_historique($d);
	}
	
	public function get_all_date_dissolution($id) {
		$dissolutions = array();
		$query = 
			"SELECT idDate, reference, to_char(\"DATE\", 'DD/MM/SYYYY') AS \"DATE\" FROM date_historique
			WHERE idDate IN (SELECT idDate FROM dissolution
								WHERE idEntite = " . $id . ')';
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		while ($data = oci_fetch_assoc($result)) {
			$dissolutions[] = new date_historique($data);
		}
		return $dissolutions;
	}
	
	public function get_population($id, $date) {
		$query = 'SELECT nombre FROM population WHERE idEntite = ' . $id . ' AND idDate = ' . $date;
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		$r = oci_fetch_row($result);
		return $r[0];
	}
	
	public function get_average_population($id) {
		$query = 'SELECT avg(nombre) FROM population WHERE idEntite = ' . $id;
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		$r = oci_fetch_row($result);
		return $r[0];
	}
	
	public function get_zone_ocupation($id, $annee) {
		$zones = array();
		$query = 'SELECT codeISO,nomZoneGeo FROM zone_geographique
					WHERE idZoneGeo IN (
						SELECT idZoneGeo FROM zone_occupee
						WHERE idEntite = ' . $id . '
							AND idZoneOcc IN (
								SELECT idZoneOcc FROM debut_occupation
								WHERE idDate IN (SELECT idDate from date_historique WHERE "DATE" >= TO_DATE('.$annee.',\'syyyy\')))
							AND idZoneOcc IN(
								SELECT idZoneOcc FROM fin_occupation
								WHERE idDate IN (SELECT idDate from date_historique WHERE "DATE" >= TO_DATE('.$annee.',\'syyyy\'))))';
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		while ($data = oci_fetch_row($result)) {
			$zones[] = $data;
		}
		return $zones;
	}
	
	public function max_id() {
		$query = "SELECT max(idEntite) FROM entite_historique";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		$r = oci_fetch_row($result);
		return $r[0];
	}
	
	public function add($e) {
		$query = "INSERT INTO entite_historique(idEntite, typeEntite, nomEntite, descriptifEntite, wikiEntite, profondeur)
			VALUES (" . $e->get_identite() . ",'" . $e->get_typeentite() . "','" . $e->get_nomentite() . "','" . $e->get_descriptifentite() . "','" . $e->get_wikientite() . "'," . $e->get_profondeur() . ")";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		if($e->get_creation() != "") {
			$id = $this->_date->max_id()+1;
			$this->_date->add(new date_historique(array(
										"idDate" => $id, 
										"reference" => 1,
										'date' => $e->get_creation())));
			$query2 = "INSERT INTO creation(idDate, idEntite) VALUES(" . $id . ", " . $e->get_identite() . ")";
			$result = oci_parse($this->_db, $query2);
			oci_execute($result);
		}
		
		if($e->get_dissolution() != "") {
			$id = $this->_date->max_id()+1;
			$this->_date->add(new date_historique(array(
										"idDate" => $id, 
										"reference" => 1,
										'date' => $e->get_creation())));
			$query2 = "INSERT INTO dissolution(idDate, idEntite) VALUES(" . $id . ", " . $e->get_identite() . ")";
			$result = oci_parse($this->_db, $query2);
			oci_execute($result);
		}
	}
	
	public function add_creation($idEntite, $date) {
		$id = $this->_date->max_id()+1;
		$this->_date->add(new date_historique(array(
								"idDate" => $id, 
								"reference" => 0,
								'date' => $date)));
		$query = "INSERT INTO creation(idDate, idEntite) VALUES(" . $id . ", " . $idEntite . ")";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
	}
	public function add_dissolution($idEntite, $date) {
		$id = $this->_date->max_id()+1;
		$this->_date->add(new date_historique(array(
								"idDate" => $id, 
								"reference" => 0,
								'date' => $date)));
		$query = "INSERT INTO dissolution(idDate, idEntite) VALUES(" . $id . ", " . $idEntite . ")";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
	}
	
	public function delete($i) {
		$query = "DELETE FROM entite_historique WHERE idEntite = " . $i;
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
	}
	
	public function delete_date($id) {
		$this->_date->delete($id);
	}
	
	public function update($e) {
		$query = "UPDATE entite_historique
			SET typeEntite = '" . $e->get_typeentite() . 
			"', nomEntite = '" . $e->get_nomentite() .
			"', descriptifEntite = '" . $e->get_descriptifentite() .
			"', wikiEntite ='" . $e->get_wikientite() .
			"', profondeur = " . $e->get_profondeur() .
			" WHERE idEntite = " . $e->get_identite();
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		
		$query2 = "UPDATE date_historique
			SET reference = 1
			WHERE idDate = " . $e->get_creation();
		$result = oci_parse($this->_db, $query2);
		oci_execute($result);
		
		$query2 = "UPDATE date_historique
			SET reference = 0
			WHERE idDate IN (SELECT idDate FROM creation WHERE idDate != " . $e->get_creation() . ')';
		$result = oci_parse($this->_db, $query2);
		oci_execute($result);
		
		
		$query3 = "UPDATE date_historique
			SET reference = 1
			WHERE idDate = " . $e->get_dissolution();
		$result = oci_parse($this->_db, $query3);
		oci_execute($result);
		
		$query3 = "UPDATE date_historique
			SET reference = 0
			WHERE idDate IN (SELECT idDate FROM dissolution WHERE idDate != " . $e->get_dissolution() . ')';
		$result = oci_parse($this->_db, $query3);
		oci_execute($result);
	}
	
	public function add_zone($entite, $zone, $debut, $fin) {
		$id_occ = $this->_zone->max_occ_id()+1;
		$query = "INSERT INTO zone_occupee (idZoneOcc, idZoneGeo, idEntite)
				VALUES (" . $id_occ . "," . $zone . "," . $entite . ")";
		$result = oci_parse($this->_db, $query);
		oci_execute($result);

		if($debut != "") {
			$id_date = $this->_date->max_id() + 1;
			$this->_date->add(new date_historique(array(
							"idDate" => $id_date, 
							"reference" => 1,
							'date' => $debut)));

			$query2 = "INSERT INTO debut_occupation (idDate, idZoneOcc)
					VALUES (" . $id_date . ", " . $id_occ . ")";
			$result = oci_parse($this->_db, $query2);
			oci_execute($result);
		}
		
		if($fin != "") {
			$id_date = $this->_date->max_id() + 1;
			$this->_date->add(new date_historique(array(
							"idDate" => $id_date, 
							"reference" => 1,
							'date' => $fin)));

			$query2 = "INSERT INTO fin_occupation (idDate, idZoneOcc)
					VALUES (" . $id_date . ", " . $id_occ . ")";
			$result = oci_parse($this->_db, $query2);
			oci_execute($result);
		}
	}
	
	public function delete_zone($id) {
		$query = "DELETE FROM zone_occupee WHERE idZoneOcc = " . $id;
		$result = oci_parse($this->_db, $query);
		oci_execute($result);
		echo $query;
	}
}
?>
